const express = require("express");
const app = express();
app.listen(3000,()=>{
  console.log("服务器已开启");
});
const path = require('path');
const db = require("./db.js");
const template = require("express-art-template");
app.engine("html",template);
const bp = require('body-parser');
app.use(bp.urlencoded({extended:false}));
//监听/index路由(显示index.html页面)
app.get('/index',(req,res)=>{
  const sql = `select * from student
                join dept on student.sdept = dept.did
                order by student.sno asc`;
  db.query(sql,(err,result)=>{
    if(err){
      // console.log(err);
      return res.render(path.join(__dirname,'./view/index.html'),{list:[]});
    };
    // console.log(result.length)
    res.render(path.join(__dirname,'./view/index.html'),{list:result})
  });
});
//监听/add路由(显示add.html页面)
app.get('/add',(rea,res)=>{
  res.sendFile(path.join(__dirname,'./view/add.html'))
});
//监听/addstu路由(获取表单数据)
app.post('/addstu',(req,res)=>{
  // console.log(req.body);
  const sql = `insert into student set ?`
  const obj ={
    sname:req.body.name,
    sage:req.body.age,
    sgender:req.body.gender,
    stime:req.body.time,
    sdept:req.body.dept
  };
  db.query(sql,obj,(err,result)=>{
    if(err || result.affectedRows == 0 ){
      console.log(err);
     return res.redirect('/add');
    };
    // console.log(result);
    res.redirect('/index');
  });
});
//监听/delstu路由(获取数据库中的数据)
app.get('/delstu',(req,res)=>{
  // console.log(req.query);
  const sql = `delete from student where sno=?`
  db.query(sql,req.query.no,(err,result)=>{
    if(err){
      console.log(err);
    };
    // console.log(result);
    res.redirect('/index');
  });
});
//监听/edit(显示edit.html)
app.get('/edit',(req,res)=>{
  // console.log(req.query);
  const sql = `select * from student where sno=?`;
  db.query(sql,req.query.no,(err,result)=>{
    // console.log(result)
    res.render(path.join(__dirname,'./view/edit.html'),result[0])
  });
});
//监听/modifystu(获取表单修改数据)
app.post('/modifystu',(req,res)=>{
  // console.log(req.body);
  const sql = `update student set ? where sno=?`;
  const obj = {
    sname:req.body.name,
    sage:req.body.age,
    sgender:req.body.gender,
    stime:req.body.time,
    sdept:req.body.dept
  };
  db.query(sql,[obj,req.body.no],(err,result)=>{
    if(err){
      console.log(err);
      res.redirect('/edit?no='+ req.body.no);
    };
    res.redirect('/index');
  });
});